package sqldb

import (
	"reflect"
	"github.com/eyediy/dbcontrol"
	"log"
	"fmt"
	"errors"
	"database/sql"
	"strings"
)

// NewEntityHandler .
type NewEntityHandler func() (interface{})
type AddEntityHandler func(interface{}) (error)

// QueryRules .
type QueryRules struct {
	C *Cond
	S *Sort
	P *Pagination
}

// IDAO DAO interface
type IDAO interface {
	Bind(db *Database)
}

// Dao 数据访问对象
type DAO struct {
	db *Database
	Name string
	Schema string
	Type reflect.Type
}

// DB 获取数据库实例
func (m *DAO) DB() *dbcontrol.DB {
	return m.db.db
}

func (m *DAO) RegisterEntity(entity interface{}) {
	m.Type = reflect.TypeOf(entity)
}

func (m *DAO) MapEntityToValues(pEntity interface{}) []interface{} {
	var values []interface{}
	vAccessor := reflect.ValueOf(pEntity).Elem()
	for i:= 0; i<vAccessor.NumField(); i++ {
		v := vAccessor.Field(i)
		if v.CanAddr() {
			values = append(values, v.Addr().Interface())
		}
	}
	return values
}

// Bind .
func (m *DAO) Bind(db *Database) {
	m.db = db
	m.Schema = ""
	for i:=0; i<m.Type.NumField(); i++ {
		field := m.Type.Field(i)
		// add schema
		if m.Schema != "" {
			m.Schema+=","
		}
		fName, ok := field.Tag.Lookup("db");
		if !ok {
			fName = field.Name
		}
		m.Schema+=fName
	}
}

func (m *DAO) buildAddStmt(fields []string, hiddenValues []map[string]interface{}, values ...interface{}) (string, []interface{}, error) {
	var query string
	var schema = m.Schema
	var literalValueMask string
	var literalValues string
	var args []interface{}

	nHiddenValues := 0
	if hiddenValues != nil {
		nHiddenValues = len(hiddenValues)
	}
	n:= len(fields)
	if n<1 {
		return "", nil, errors.New("[DAO::buildAddStmt] fields must be set")
	}
	schema = ""
	for i:=0; i<n; i++ {
		fName := fields[i]
		f, ok := m.Type.FieldByName(fName)
		if ok {
			if meta_db, ok := f.Tag.Lookup("db"); ok {
				fName = meta_db
			}
		}
		if schema != "" {
			schema+=","
		}
		schema+=fName
	}

	literalValues = ""
	iArg := 1
	nValues := len(values)
	for i:=0; i<nValues; i++ {
		vAccessor := reflect.ValueOf(values[i]).Elem()
		literalValueMask = ""
		for j:=0; j<n; j++ {
			v := vAccessor.FieldByName(fields[j])
			if v.IsValid() {
				args = append(args, v.Interface())
			} else {
				// 如果指定的字段不属于实体，则表示该字段为隐藏字段
				if nHiddenValues==nValues {
					// 如果包含隐藏值的话，不许保证和值列表的长度一致
					args = append(args, hiddenValues[i][fields[j]])
				}
			}
			if literalValueMask == "" {
				literalValueMask += "("
			}
			if literalValueMask != "(" {
				literalValueMask += ","
			}
			literalValueMask += fmt.Sprint("$", iArg)
			iArg++
		}
		if literalValueMask != "" {
			literalValueMask += ")"
		}
		if literalValues != "" {
			literalValues += ","
		}
		literalValues += literalValueMask
	}

	query = "INSERT INTO " + m.Name + " (" + schema + ") VALUES " + literalValues
	return query, args, nil
}

// Add 增
//	Parameters:
//	tx		事务指针
// 	fields 		字段数组
//	hiddenValues	隐藏字段的值列表，用来设置隐藏字段的值
//	values		实体列表
//	Returns:
//	_result 常规结果
//	_err	错误
func (m *DAO) Add(tx *dbcontrol.Tx, fields []string, hiddenValues []map[string]interface{}, values ...interface{}) (_result sql.Result, _err error) {
	defer func(){
		if err := recover(); err != nil {
			_result = nil
			_err = errors.New(fmt.Sprint("[DAO::Add unhandled] ", err))
		}
	}()

	query, args, err := m.buildAddStmt(fields, hiddenValues, values...)
	if err != nil {
		return nil, err
	}
	if m.db.DebugMode {
		log.Print("[DAO::Add] ", query, " ", args)
	}
	if tx != nil {
		return tx.Exec(query, args...)
	}
	return m.DB().Exec(query, args...)
}

// AddWithReturningID 添加并返回ID列表
//	参数：
// 	tx		事务指针
// 	idName		ID字段名
// 	fields		字段列表
//	hiddenValues	隐藏字段的值列表，用来设置隐藏字段的值
// 	values		要添加的实体指针列表
//	结果:
//	1 ID列表
//	2 错误
func (m *DAO) AddWithReturningID(tx *dbcontrol.Tx, idName string, fields []string, hiddenValues []map[string]interface{}, values ...interface{}) (_result []int64, _err error) {
	defer func(){
		if err := recover(); err != nil {
			_result = nil
			_err = errors.New(fmt.Sprint("[DAO::AddWithReturningID unhandled] ", err))
		}
	}()

	var idList []int64
	var query string
	var args []interface{}
	var err error
	var rows *dbcontrol.Rows

	query, args, err = m.buildAddStmt(fields, hiddenValues, values...)
	if err != nil {
		return nil, err
	}
	var colName = idName
	if field, ok := m.Type.FieldByName(idName); ok {
		if tag_db, ok := field.Tag.Lookup("db"); ok {
			colName = tag_db
		}
	}
	query += " RETURNING "+colName
	if m.db.DebugMode {
		log.Print("[DAO::AddWithReturningID] ", query, " ", args)
	}
	if tx != nil {
		// 事务模式
		var txRows *sql.Rows
		txRows, err = tx.Query(query, args...)
		if err != nil {
			return nil, err
		}
		for txRows.Next() {
			var id int64
			if err:= txRows.Scan(&id); err != nil {
				return nil, errors.New(fmt.Sprint("[DAO::AddWithReturningID txRows.Scan(&id)] ", err))
			}
			idList = append(idList, id)
		}
		return idList, nil
	}
	rows, err = m.DB().Query(query, args...)
	if err != nil {
		return nil, err
	}
	for rows.Next() {
		var id int64
		if err:= rows.Scan(&id); err != nil {
			return nil, err
		}
		idList = append(idList, id)
	}
	return idList, nil
}

//
func (m *DAO) buildWhereStmt(qr *QueryRules, iArgStart int) (string, []interface{}, error) {
	var args []interface{}
	iArg := iArgStart
	var rules = ""
	if qr != nil {
		var literal string
		literal = ""
		if qr.C != nil {
			for i:=0; i<len(qr.C.Clauses); i++ {
				clause := qr.C.Clauses[i]
				key := clause.Key
				if literal != "" {
					literal += " " + clause.AndOr
				}
				if field, ok := m.Type.FieldByName(key); ok {
					if fName, ok := field.Tag.Lookup("db"); ok {
						key = fName
					}
				}
				args = append(args, clause.Value)
				literal += fmt.Sprint(" ", key, clause.Op, "$", iArg)
				iArg++
			}
		}
		if literal != "" {
			rules += " WHERE" + literal
		}

		literal = ""
		if qr.S != nil {
			for i:=0; i<len(qr.S.Clauses); i++ {
				clause := qr.S.Clauses[i]
				key := clause.Expr
				if literal != "" {
					literal += ","
				}
				if field, ok := m.Type.FieldByName(key); ok {
					if fName, ok := field.Tag.Lookup("db"); ok {
						key = fName
					}
				}
				literal += key + " " + clause.Order
			}
		}
		if literal != "" {
			rules += " ORDER BY " + literal
		}

		if qr.P != nil {
			rules += qr.P.String()
		}
	}
	return rules, args, nil
}

// Query 查询
func (m *DAO) Query(qr *QueryRules, pEntity interface{}, addEntity AddEntityHandler) (_err error) {
	var err error
	var query string
	var rows *dbcontrol.Rows

	defer func(){
		if err := recover(); err != nil {
			_err = errors.New(fmt.Sprint("[DAO::Read unhandled] ", err))
		}
	}()

	var args []interface{}
	var rules string
	rules, args, err = m.buildWhereStmt(qr, 1)
	if err != nil {
		return err
	}
	query = "SELECT " + m.Schema + " FROM "+ m.Name + rules
	if m.db.DebugMode {
		log.Print("[DAO::Read] ", query, " ", args)
	}

	rows, err = m.DB().Query(query, args...)
	if err != nil {
		return err
	}
	for rows.Next() {
		values := m.MapEntityToValues(pEntity)
		if err:= rows.Scan(values...); err != nil {
			return err
		}
		addEntity(pEntity)
	}
	return nil
}

// Delete 删除操作
func (m *DAO) Delete(tx *dbcontrol.Tx, qr *QueryRules) (_result sql.Result, _err error) {
	var err error
	var args []interface{}
	var rules string
	var query string

	defer func(){
		if err := recover(); err != nil {
			_err = errors.New(fmt.Sprint("[DAO::Delete unhandled] ", err))
		}
	}()

	rules, args, err = m.buildWhereStmt(qr, 1)
	if err != nil {
		return nil, err
	}
	if rules == "" {
		return nil, errors.New("[DAO::Delete] it is disallowed to clear the entire table!")
	}
	query = "DELETE FROM " + m.Name + rules
	if m.db.DebugMode {
		log.Print("[DAO::Delete] ", query, " ", args)
	}

	if tx != nil {
		return tx.Exec(query, args...)
	}
	return m.DB().Exec(query, args...)
}

// Update 更新操作
func (m *DAO) Update(tx *dbcontrol.Tx, fields []string, hiddenValues map[string]interface{}, value interface{}, qr *QueryRules) (_result sql.Result, _err error) {
	var err error
	var args []interface{}
	var qrArgs []interface{}
	var rules string
	var query string

	defer func(){
		if err := recover(); err != nil {
			_err = errors.New(fmt.Sprint("[DAO::Update unhandled] ", err))
		}
	}()

	iArg := 1
	n:= len(fields)
	if n<1 {
		return nil, errors.New("[DAO::Update] fields must be set")
	}
	setClause := ""
	vAccessor := reflect.ValueOf(value).Elem()
	for i:=0; i<n; i++ {
		if setClause != "" {
			setClause+=","
		}
		fName := fields[i]
		f, ok := m.Type.FieldByName(fName)
		if ok {
			if meta_db, ok := f.Tag.Lookup("db"); ok {
				fName = meta_db
			}
			v := vAccessor.FieldByIndex(f.Index)
			args = append(args, v.Interface())
		} else {
			hiddenVal := hiddenValues[fName]
			if expr, ok := hiddenVal.(SetValueExpression); ok {
				kv := strings.Split(expr.Expr, "?")
				nParts := len(kv)
				if nParts<2 && len(expr.Args)>=(nParts-1) {
					return nil, errors.New("[DAO::Update] expr must be valid!")
				}
				setClause+=fName+"="+kv[0]
				for h:=1; h<nParts; h++ {
					args = append(args, expr.Args[h-1])
					setClause+=fmt.Sprint("$", iArg, kv[h])
					iArg++
				}
				continue
			}
			args = append(args, hiddenVal)
		}
		setClause+=fmt.Sprint(fName, "=$", iArg)
		iArg++
	}

	rules, qrArgs, err = m.buildWhereStmt(qr, len(args)+1)
	if err != nil {
		return nil, err
	}
	if rules == "" {
		return nil, errors.New("[DAO::Update] it is disallowed to update the entire table!")
	}
	args = append(args, qrArgs...)
	query = "UPDATE " + m.Name + " SET " + setClause + rules
	if m.db.DebugMode {
		log.Print("[DAO::Update] ", query, " ", args)
	}

	if tx != nil {
		return tx.Exec(query, args...)
	}
	return m.DB().Exec(query, args...)
}
